/*
 AMENDMENTS:
 DATE          NAME        VERSION     DESCRIPTION
--------       -----       -------     -----------
 18/04/2013    MKANG       	1.0.0       1.SysLogHome.java .
*/

package com.mbbmap.app.home;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.ArrayList;

import com.mbbmap.app.dao.Pagination;
import com.mbbmap.app.dao.SysLogDao;
import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;
import com.mbbmap.app.manager.SystemLogManager;
import com.mbbmap.util.Constants;
import com.mbbmap.util.DateUtil;
import com.mbbmap.util.EnhancedException;

public class SysLogHome {
	private static SysLogHome instance = null;
	 private static final String tblSysLog = "TBLSYSLOG";
	 private static final int USER_FIELD1_LENGTH = 500;
	 private static final int USER_FIELD2_LENGTH = 255;
	 private static final int USER_FIELD3_LENGTH = 255;
	 private static final int USER_FIELD4_LENGTH = 255;

	//static final SimpleDateFormat currentDateFormatter = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");

		/**
		 * Returns the single instance, creating one if it's the
		 * first time this method is called.
		 *
		 * @return SysLogHome. The single instance.
		 */
	public static SysLogHome getInstance() {
		//thread-safe implementation
		if (instance == null) {
			synchronized (SysLogHome.class) {
				if (instance == null) {
					instance = new SysLogHome();
				}
			}
		}
		return instance;
	}

	private SysLogHome() {
		super();
	}

	/**
	 * added by ronnie for Sys Log into database(20090122)
	 * lhlue updated 20090830
	 */
	public void insertSysLog(SysLogDao sysLogDao)
	{
		  	String querySql = null;

			int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
			DBConnectionManager connMgr;
			String connectionPool;
			SystemLogManager sysLogMgr;

			connectionPool = ConfigManager.getInstance().getConnectionPool();
			connMgr = DBConnectionManager.getInstance();
			String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
			if (timeOutValue != null) {
				try {
					TIMEOUT = Integer.parseInt(timeOutValue);
				} catch (NumberFormatException e) {}
			}
			sysLogMgr = SystemLogManager.getInstance();

			Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

			PreparedStatement pstmtInsert = null;

			//Calendar cal = Calendar.getInstance();
			//String currentDate = currentDateFormatter.format(cal.getTime()).toString();


			try {
				if (conn == null) {
					EnhancedException ex = new EnhancedException("");
					ex.setExceptionLocation("insertSysLog [Connection at : " + connectionPool + "]");
					ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
					sysLogMgr.logError(ex);
				}
				else{
		    		StringBuffer sqlStmt = new StringBuffer("");

		    		//Truncate user field that exceed the max length
		    		String userField1 = sysLogDao.getUserField1();
		    		String userField2 = sysLogDao.getUserField2();
		    		String userField3 = sysLogDao.getUserField3();
		    		String userField4 = sysLogDao.getUserField4();

		    		if (userField1.length() > USER_FIELD1_LENGTH) {
		    		    userField1 = userField1.substring(0, USER_FIELD1_LENGTH-1);
		    		}
		    		if (userField2.length() > USER_FIELD2_LENGTH) {
		    		    userField2 = userField2.substring(0, USER_FIELD2_LENGTH-1);
		    		}
		    		if (userField3.length() > USER_FIELD3_LENGTH) {
		    		    userField3 = userField3.substring(0, USER_FIELD3_LENGTH-1);
		    		}
		    		if (userField4.length() > USER_FIELD4_LENGTH) {
		    		    userField4 = userField4.substring(0, USER_FIELD4_LENGTH-1);
		    		}


					querySql = "INSERT INTO " + tblSysLog
							   + "(ACTIVITYCODE, ACTIVITYNAME, ACCTID, USERID, ROLE, STATUS, DATE1, DATE2, USERFIELD1, USERFIELD2, USERFIELD3, USERFIELD4) "
							   + "VALUES ('" + sysLogDao.getActivityCode()
							   + "','" + sysLogDao.getActivityName()
							   + "','" + sysLogDao.getAcctID()
							   + "','" + sysLogDao.getUserID()
							   + "','" + sysLogDao.getRole()
							   + "','" + sysLogDao.getStatus()
							   + "', ?"
							   + ", ?"
							   + ",'" + userField1
							   + "','" + userField2
							   + "','" + userField3
							   + "','" + userField4
							   + "')";

					//System.out.println("insertSysLog querySql=>"+querySql);

					java.util.Date now = new java.util.Date();
					sqlStmt.append(querySql);
					pstmtInsert = conn.prepareStatement(sqlStmt.toString());

					if (sysLogDao.getDate1() == null) {
					    pstmtInsert.setTimestamp(1, new Timestamp(now.getTime()));
					} else {
					    pstmtInsert.setTimestamp(1, new Timestamp(sysLogDao.getDate1().getTime()));
					}

					if (sysLogDao.getDate2() == null) {
					    pstmtInsert.setTimestamp(2, new Timestamp(now.getTime()));
					} else {
					    pstmtInsert.setTimestamp(2, new Timestamp(sysLogDao.getDate2().getTime()));
					}

					int countUpdated = pstmtInsert.executeUpdate();
					System.out.println("SysLogHome.insertSysLog updated =" + countUpdated);
					pstmtInsert.close();

				}

		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation("insertSysLog [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation("insertSysLog [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (pstmtInsert != null) {
				try {
					pstmtInsert.close();
				} catch (SQLException e1) {}
			}
			connMgr.freeConnection(connectionPool, conn);
		}


	}
	/** added by lhlue
	 * Get last activity timestamp for the given login Id
	 * @param loginId
	 * @return
	 */
	public java.util.Date getDateLastAccess (String loginId) {

        String querySql = null;
        java.util.Date lastAccessedDate = null;
        Connection connection=null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dba = DBAccessHome.getInstance();

        try {
            connection = dba.getDBConnection();

            if (loginId == null || loginId.trim().equals("") ) {
                throw new Exception("loginId is null or blank");
            }

            if (connection == null) {
                System.err.println("SysLogHome.getDateLastAccess(): DB Connection is null");
                throw new Exception ("DB Connection is null");
            }
            querySql = "select max(date1) from " + tblSysLog + " where userid=? " ;
            pstmtQuery =  connection.prepareStatement(querySql);
            pstmtQuery.setString(1, loginId);

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
                lastAccessedDate = rs.getTimestamp(1);
            }
            rs.close();

        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("SysLogHome.getDateLastAccess() : Error in SQL execution : " + querySql + "\n" + sqle);
            sqle.printStackTrace();
        } catch (Exception ex) {
            sysLogMgr.logMsg("Error in SysLogHome.getDateLastAccess() : \n" + ex);
            ex.printStackTrace();
        } catch (Throwable t) {
            sysLogMgr.logMsg("Error in SysLogHome.getDateLastAccess() : \n" + t);
            t.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
		            sysLogMgr.logMsg("Error in SysLogHome.getDateLastAccess() : Error closing pstmtQuery\n" + e1);
		            e1.printStackTrace();
				}
			}

            dba.freeDBConnection (connection);
		}

        return lastAccessedDate;
	}


    /**
     *  Get the Sys Log records
     *  Search criteria as given by the parameters
     * @param merchantId
     * @param loginId
     * @param agencyCode
     * @param actDateFrom in format : "dd/MM/yyyy"
     * @param actDateTo in format : "dd/MM/yyyy"
     * @param actCode Activity Code
     * @param actName Activity Name
     * @param actStatus Activity Status code
     * @param page Pagination class - to setup records to select and return
     * @return ArrayList of SysLogDao
     */
    public ArrayList getSysLogs (String merchantId, String loginId, String agencyCode, String actDateFrom, String actDateTo, String actCode, String actName, String actStatus, String userField1, Pagination page) {

        ArrayList sysLogs = new ArrayList();
        java.util.Date dtActDateFrom = null, dtActDateTo = null;
        StringBuffer querySql = new StringBuffer("SELECT ACTIVITYCODE, ACTIVITYNAME, ACCTID, USERID, ROLE, STATUS, DATE1, DATE2, USERFIELD1, USERFIELD2 FROM "
                + tblSysLog);
        Connection connection = null;
        PreparedStatement pstmtQuery = null;
        DBAccessHome dba = DBAccessHome.getInstance();
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        boolean bLoginId = false, bActDateFrom=false, bActDateTo=false, bActCode=false, bActName=false, bActStatus=false, bUserField1=false;

        try {
            StringBuffer tmpSql = new StringBuffer("");

            if (loginId !=null  && !loginId.trim().equals("")) {
                bLoginId = true;
                tmpSql.append(" LOWER(USERID) LIKE ? ");
            }

            if (actDateFrom !=null  && !actDateFrom.trim().equals("")) {
                bActDateFrom = true;
                if (!tmpSql.toString().trim().equals(""))
                    tmpSql.append(" AND ");

                tmpSql.append(" DATE1 >=? ");
                dtActDateFrom = DateUtil.createDateLocalSlash(actDateFrom.trim());
            }

            if (actDateTo !=null  && !actDateTo.trim().equals("")) {
                bActDateTo = true;
                if (!tmpSql.toString().trim().equals(""))
                    tmpSql.append(" AND ");

                tmpSql.append(" DATE1 <=? ");
                dtActDateTo = DateUtil.createDateLocalSlash(actDateTo.trim());
            }

            if (actCode !=null  && !actCode.trim().equals("")) {
                bActCode = true;
                if (!tmpSql.toString().trim().equals(""))
                    tmpSql.append(" AND ");

                tmpSql.append(" LOWER(ACTIVITYCODE) LIKE ? ");
            }

            if (actName !=null  && !actName.trim().equals("")) {
                bActName = true;
                if (!tmpSql.toString().trim().equals(""))
                    tmpSql.append(" AND ");

                tmpSql.append(" LOWER(ACTIVITYNAME) LIKE ? ");
            }

            if (actStatus !=null  && !actStatus.trim().equals("")) {
                bActStatus = true;
                if (!tmpSql.toString().trim().equals(""))
                    tmpSql.append(" AND ");

                tmpSql.append(" LOWER(STATUS) LIKE ? ");
            }

            if (userField1 !=null  && !userField1.trim().equals("")) {
                bUserField1 = true;
                if (!tmpSql.toString().trim().equals(""))
                    tmpSql.append(" AND ");

                tmpSql.append(" LOWER(USERFIELD1) LIKE ? ");
            }

            /////////////////////////////////////////////////////////////

            if (!tmpSql.toString().trim().equals("")) {
                querySql.append(" WHERE ").append(tmpSql);
            }

            querySql.append(" ORDER BY USERID, DATE1 DESC, ACTIVITYCODE, ACTIVITYNAME ");

            System.out.println("SysLogHome.getSysLogs() : SQL=" + querySql.toString());

            connection = dba.getDBConnection();
            pstmtQuery =  connection.prepareStatement(querySql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            int col=1;

            if (bLoginId) {
                pstmtQuery.setString(col, "%"+loginId.toLowerCase().trim()+"%");
                col++;
            }

            if (bActDateFrom) {
                pstmtQuery.setDate(col, new java.sql.Date(dtActDateFrom.getTime()));
                col++;
            }

            if (bActDateTo) {
                pstmtQuery.setDate(col, new java.sql.Date(dtActDateTo.getTime()));
                col++;
            }

            if (bActCode) {
                pstmtQuery.setString(col, "%"+actCode.toLowerCase().trim()+"%");
                col++;
            }

            if (bActName) {
                pstmtQuery.setString(col, "%"+actName.toLowerCase().trim()+"%");
                col++;
            }

            if (bActStatus) {
                pstmtQuery.setString(col, "%"+actStatus.toLowerCase().trim()+"%");
                col++;
            }

            if (bUserField1) {
                pstmtQuery.setString(col, "%"+userField1.toLowerCase().trim()+"%");
                col++;
            }

            // only able to view activity logs of users
            // that belongs to logon user's child agency
            pstmtQuery.setString(col, agencyCode);
            col++;
            pstmtQuery.setString(col, merchantId);

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            SysLogDao sysLog;

            // if no result return null
            if (!rs.last()) {
                rs.close();
                //pstmtQuery.close();
                //connection.close();
                sysLogs = null;

            } else {
                int noOfRows = rs.getRow();
                if (page.getTotalRecord() <= 0 ) {
                    page.setTotalRecord(noOfRows);
                }
                //rs.beforeFirst();
                int startRow = page.getPageRecordStartNo();
                int endRow = page.getPageRecordEndNo();

                // move cursor to the row to read
                rs.absolute(startRow);
                int counter = startRow;

                do {
                    sysLog = new SysLogDao();

                    sysLog.setActivityCode(rs.getString(1));
                    sysLog.setActivityName(rs.getString(2));
                    sysLog.setAcctID(rs.getString(3));
                    sysLog.setUserID(rs.getString(4));
                    sysLog.setRole(rs.getString(5));
                    sysLog.setStatus(rs.getString(6));
                    sysLog.setDate1(new java.util.Date(rs.getTimestamp(7).getTime()));
                    sysLog.setDate2(new java.util.Date(rs.getTimestamp(8).getTime()));
                    sysLog.setUserField1(rs.getString(9));
                    sysLog.setUserField2(rs.getString(10));
                    sysLogs.add(sysLog);
                    counter++;
                } while (counter <= endRow && rs.next());
                pstmtQuery.close();
                rs.close();
            }
        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("SysLogHome.getSysLogs() Error in SQL execution : " + querySql + "\n" + sqle);
            sqle.printStackTrace();
            sysLogs = null;
        } catch (ParseException pe) {
            sysLogMgr.logMsg("SysLogHome.getSysLogs() Error in input date format" + pe);
            pe.printStackTrace();
            sysLogs = null;
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
		            sysLogMgr.logMsg("SysLogHome.getSysLogs() Error closing pstmtQuery\n" + e1);
		            e1.printStackTrace();
				}
			}
            dba.freeDBConnection (connection);
		}

        return sysLogs;

    }




}
